To work through these examples, you will need the following on your own computer:
Excel (you have a licence for this via the university)Excel does not have an option to produce a 1D scatterplot, natively. We therefore produce only a boxplot (also known as a box-and-whisker plot)
You cannot easily convert the data in toothgrowth.tab to the format you need, in order to create a boxplot, using only Excel. The original file presents data in long format (one row per observation). To construct a boxplot in Excel, the data must be in wide format. This requires either a lot of function programming in Excel, or the use of the Power Query plugin, both of which are beyond the scope of this workshop.
We are instead providing a pre-prepared dataset: toothgrowth_wide.tab so that you can construct a boxplot in Excel:
R
Being a statistical programming package, R has several tools for reshaping datasets. We used the tidyr package function pivot_wider and some dplyr convenience functions to modify the data, then save it, using the commands below:
widedata = ToothGrowth %>%
group_by(supp, dose) %>%
mutate(row=row_number()) %>%
pivot_wider(names_from=c(supp, dose), values_from=len)
write.table(widedata, "data/toothgrowth_wide.tab",
sep="\t", row.names=FALSE)
Our goal is to show how the measured guinea pig tooth growth varies by combination of supplement and supplement dosage. We could approach this in any of several ways, but here we want to treat each supplement as a category or factor, and each dosage as a category or factor. We’d like to see the distribution of measured tooth lengths conditioned on these explanatory variables.
What we’re looking for is a visual representation of the variation in the dataset, for each combination of supplement and dosage. Normally, a 1D scatterplot is a good way to visualise the raw data, with a boxplot/box-and-whisker plot to represent summary statistics. Sadly, Excel cannot represent a 1D scatterplot easily, so we only show the box-and-whisker plot.
BlankWorkbook` in Excel
Figure 1.1: Create a blank workbook in Excel
Figure 1.2: Create a blank workbook in Excel
File \(\rightarrow\) Import to begin the file import wizard.toothgrowth_wide.tab fileFigure 1.3: Select data file for import
Figure 1.4: Select data file for import
Figure 1.5: Select import options
Figure 1.6: Select import options
Figure 1.7: Select import options
Figure 1.8: Select import options
Figure 1.9: Select import options
At this point the data are in six columns, identified as a combination of supplement (VC or OJ) and supplement dose (one of 0.5, 1, or 2 mg/day). Each measurement is tooth length from a single guinea pig.
To create the box-and-whisker plot, select the complete dataset (including headers), then click on Insert and select the statistical plots button from the ribbon, then the Box-and-Whisker option in the dropdown menu.
Figure 1.10: Select box-and-whisker plot
Figure 1.11: Select box-and-whisker plot
This will generate a new graph in your worksheet.
The graph is, by default, not informatively-labelled.
Add Chart Element \(\rightarrow\) Legend \(\rightarrow\) Right menu to add a legend to the graph.Figure 1.12: Tidy plot details
Figure 1.13: Tidy plot details